package com.ssi.mrms.dao.Impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.ssi.mrms.dao.KeyCardDao;
import com.ssi.mrms.model.DataTableModel;
import com.ssi.mrms.model.RMactwModel;

@Repository("keyCardDao")
public class KeyCardDaoImpl implements KeyCardDao{
	
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public void insert(RMactwModel model) {
		
		String sql = "insert into `rmactw`(" +
				"`ACTWID`," +
				"`RLRMID`," +
				"`ACTWUSRNO`," +
				"`ACTWCRDNO`," +
				"`ACTWSTAT`," +
				"`ACTWSTATDAT`," +
				"`ACTWACUPD`," +
				"`ACTWCREBY`," +
				"`ACTWCREDAT`," +
				"`ACTWUPDBY`," +
				"`ACTWUPDDAT`) " +
				"values ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )";
		
		@SuppressWarnings("deprecation")
		int nextRow = jdbcTemplate.queryForInt("SELECT MAX(ACTWID)+1 FROM rmactw");
	
		jdbcTemplate.update(sql, 
//				model.getActwid(),
				nextRow,
				model.getRlrmidKey(),
				model.getActwusrno(),
				model.getActwcrdno(),
				model.getActwstat(),
				model.getActwstatdat(),
				model.getActwacupd(),
				model.getActwcreby(),
				model.getActwcredat(),
				model.getActwupdby(),
				model.getActwupddat()
				
				);
	}

	@Override
	public void update(RMactwModel model) {
		String sql = "update `rmactw` set " +
				"`ACTWID`= ?," +
				"`RLRMID`= ? ," +
				"`ACTWUSRNO`= ?," +
				"`ACTWCRDNO`= ?," +
				"`ACTWSTAT`= ? ," +
				"`ACTWSTATDAT`= ? ," +
				"`ACTWACUPD`= ?," +
				"`ACTWCREBY`= ? ," +
				"`ACTWCREDAT`= ? ," +
				"`ACTWUPDBY`= ? ," +
				"`ACTWUPDDAT`= ? " +
				"where " +
				"`ACTWID`= ? ";
		
	
		jdbcTemplate.update(sql, 
				model.getActwid(),
				model.getRlrmidKey(),
				model.getActwusrno(),
				model.getActwcrdno(),
				model.getActwstat(),
				model.getActwstatdat(),
				model.getActwacupd(),
				model.getActwcreby(),
				model.getActwcredat(),
				model.getActwupdby(),
				model.getActwupddat(),
				model.getActwid()
				);
		
	}

	@Override
	public void delete(RMactwModel model) {
		String sql = "delete from `rmactw` where `ACTWID`=?";
		jdbcTemplate.update(sql, model.getActwid());
		System.out.println("#  delete #" + sql);
	}

	@Override
	public RMactwModel find(String actwid) {
		String sql = "SELECT * FROM rmactw WHERE ACTWID=" + actwid;
		RMactwModel obj = jdbcTemplate.queryForObject( sql , new RMactwModelMapper());
		return obj;
	}
	
	public class RMactwModelMapper implements RowMapper<RMactwModel>{

		@Override
		public RMactwModel mapRow(ResultSet rs, int rowNum) throws SQLException {
			RMactwModel r = new RMactwModel();
			r.setActwid(rs.getInt("ACTWID"));
			r.setRlrmidKey(rs.getInt("RLRMID"));
			r.setActwusrno(rs.getString("ACTWUSRNO"));
			r.setActwcrdno(rs.getString("ACTWCRDNO"));
			r.setActwstat(rs.getString("ACTWSTAT"));
			r.setActwstatdat(rs.getDate("ACTWSTATDAT"));
			r.setActwacupd(rs.getDate("ACTWACUPD"));
			r.setActwcreby(rs.getString("ACTWCREBY"));
			r.setActwcredat(rs.getDate("ACTWCREDAT"));
			r.setActwupdby(rs.getString("ACTWUPDBY"));
			r.setActwupddat(rs.getDate("ACTWUPDDAT"));
			return r;
		}
		
	}

	public class RMactwModelMapping implements RowMapper<RMactwModel>{

		@Override
		public RMactwModel mapRow(ResultSet rs, int rowNum) throws SQLException {
			RMactwModel r = new RMactwModel();
				r.setActwid( rs.getInt("ACTWID"));
				r.setRlrmidKey(rs.getInt("RLRMID"));
				r.setActwusrno(rs.getString("ACTWUSRNO"));
				r.setActwcrdno(rs.getString("ACTWCRDNO"));
				r.setActwstat(rs.getString("ACTWSTAT"));
				r.setActwstatdat(rs.getDate("ACTWSTATDAT"));
				r.setActwacupd(rs.getDate("ACTWACUPD"));
				r.setActwcreby(rs.getString("ACTWCREBY"));
				r.setActwcredat(rs.getDate("ACTWCREDAT"));
				r.setActwupdby(rs.getString("ACTWUPDBY"));
				r.setActwupddat(rs.getDate("ACTWUPDDAT"));
			return r;
		}
		
	}

	
	@Override
	public DataTableModel<RMactwModel> getUsersDataTable(RMactwModel model,int iDisplayStart, int iDisplayLength) {
		DataTableModel<RMactwModel> dataTableModel = new DataTableModel<RMactwModel>();
        String sqlLimit = MessageFormat.format(" limit {0},{1} ", new Object[]{iDisplayStart, iDisplayLength});

		String sql = "SELECT * FROM rmactw " + sqlLimit;

		List<RMactwModel> r = jdbcTemplate.query(sql, new RMactwModelMapping());
		
		String sqlCount = "SELECT count(1) FROM rmactw ";
		
		@SuppressWarnings("deprecation")
		int count = jdbcTemplate.queryForInt(sqlCount);
		
        dataTableModel.setItems(r);
        dataTableModel.setTotal(count);
        dataTableModel.setIndex(iDisplayStart);
        dataTableModel.setLength(iDisplayLength);
        
        System.out.println("SQL : " + sql);
		return dataTableModel;
	}




}
